<!DOCTYPE html>
<html lang="en">
<head>
    <title id='Description'>Implementing jqxGrid server sorting, paging and filtering with JSP and MySQL.</title>
    <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
    <script type="text/javascript" src="../../scripts/jquery-1.11.1.min.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxlistbox.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.filter.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.sort.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.pager.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdropdownlist.js"></script>
    <script type="text/javascript" src="../../scripts/demos.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            var source = {
                datatype: "json",
                datafields: [{
                    name: 'FirstName',
                    type: 'string'
                }, {
                    name: 'LastName',
                    type: 'string'
                }, {
                    name: 'Title',
                    type: 'string'
                }, {
                    name: 'BirthDate',
                    type: 'date'
                }],
                cache: false,
                url: 'jsp/populate-grid.jsp',
                filter: function () {
                    // update the grid and send a request to the server.
                    $("#jqxgrid").jqxGrid('updatebounddata', 'filter');
                },
                sort: function () {
                    // update the grid and send a request to the server.
                    $("#jqxgrid").jqxGrid('updatebounddata', 'sort');
                },
                beforeprocessing: function (data) {
                    if (data != null && data.length > 0) {
                        source.totalrecords = data[0].totalRecords;
                    }
                }
            };
            var filterChanged = false;
            var dataadapter = new $.jqx.dataAdapter(source, {
                formatData: function (data) {
                    if (data.sortdatafield && data.sortorder) {
                        // update the $orderby param of the OData service.
                        // data.sortdatafield - the column's datafield value(ShipCountry, ShipCity, etc.).
                        // data.sortorder - the sort order(asc or desc).
                        data.$orderby = data.sortdatafield + " " + data.sortorder;
                    }

                    if (data.filterscount) {
                        var where = " WHERE (";
                        var tmpdatafield = "";
                        var tmpfilteroperator = "";
                        for (var i = 0; i < data.filterscount; i++) {
                            // get the filter's value.
                            var filtervalue = data['filtervalue' + i];
                            // get the filter's condition.
                            var filtercondition = data['filtercondition' + i];
                            // get the filter's column.
                            var filterdatafield = data['filterdatafield' + i];
                            // get the filter's operator.
                            var filteroperator = data['filteroperator' + i];

                            if (tmpdatafield == "") {
                                tmpdatafield = filterdatafield;
                            } else if (tmpdatafield != filterdatafield) {
                                where += ")AND(";
                            } else if (tmpdatafield == filterdatafield) {
                                if (tmpfilteroperator == 0) {
                                    where += " AND ";
                                } else where += " OR ";
                            }

                            // build the "WHERE" clause depending on the filter's condition, value and datafield.
                            switch (filtercondition) {
                                case "NOT_EMPTY":
                                case "NOT_NULL":
                                    where += " " + filterdatafield + " NOT LIKE '" + "" + "'";
                                    break;
                                case "EMPTY":
                                case "NULL":
                                    where += " " + filterdatafield + " LIKE '" + "" + "'";
                                    break;
                                case "CONTAINS_CASE_SENSITIVE":
                                    where += " BINARY  " + filterdatafield + " LIKE '%" + filtervalue + "%'";
                                    break;
                                case "CONTAINS":
                                    where += " " + filterdatafield + " LIKE '%" + filtervalue + "%'";
                                    break;
                                case "DOES_NOT_CONTAIN_CASE_SENSITIVE":
                                    where += " BINARY " + filterdatafield + " NOT LIKE '%" + filtervalue + "%'";
                                    break;
                                case "DOES_NOT_CONTAIN":
                                    where += " " + filterdatafield + " NOT LIKE '%" + filtervalue + "%'";
                                    break;
                                case "EQUAL_CASE_SENSITIVE":
                                    where += " BINARY " + filterdatafield + " = '" + filtervalue + "'";
                                    break;
                                case "EQUAL":
                                    where += " " + filterdatafield + " = '" + filtervalue + "'";
                                    break;
                                case "NOT_EQUAL_CASE_SENSITIVE":
                                    where += " BINARY " + filterdatafield + " <> '" + filtervalue + "'";
                                    break;
                                case "NOT_EQUAL":
                                    where += " " + filterdatafield + " <> '" + filtervalue + "'";
                                    break;
                                case "GREATER_THAN":
                                    where += " " + filterdatafield + " > '" + filtervalue + "'";
                                    break;
                                case "LESS_THAN":
                                    where += " " + filterdatafield + " < '" + filtervalue + "'";
                                    break;
                                case "GREATER_THAN_OR_EQUAL":
                                    where += " " + filterdatafield + " >= '" + filtervalue + "'";
                                    break;
                                case "LESS_THAN_OR_EQUAL":
                                    where += " " + filterdatafield + " <= '" + filtervalue + "'";
                                    break;
                                case "STARTS_WITH_CASE_SENSITIVE":
                                    where += " BINARY " + filterdatafield + " LIKE '" + filtervalue + "%'";
                                    break;
                                case "STARTS_WITH":
                                    where += " " + filterdatafield + " LIKE '" + filtervalue + "%'";
                                    break;
                                case "ENDS_WITH_CASE_SENSITIVE":
                                    where += " BINARY " + filterdatafield + " LIKE '%" + filtervalue + "'";
                                    break;
                                case "ENDS_WITH":
                                    where += " " + filterdatafield + " LIKE '%" + filtervalue + "'";
                                    break;
                            }

                            if (i == data.filterscount - 1) {
                                where += ")";
                            }

                            tmpfilteroperator = filteroperator;
                            tmpdatafield = filterdatafield;
                        }

                        data.where = where;
                    } else {
                        if (filterChanged) {
                            source.totalRecords = 0;
                            filterChanged = false;
                        }
                    }
                    if (source.totalRecords) {
                        // update the $skip and $top params of the OData service.
                        // data.pagenum - page number starting from 0.
                        // data.pagesize - page size
                        data.$skip = data.pagenum * data.pagesize;
                        data.$top = data.pagesize;
                    }
                    return data;
                },
                downloadComplete: function (data, status, xhr) {
                    if (!source.totalRecords) {
                        source.totalRecords = data.length;
                    }
                },
                loadError: function (xhr, status, error) {
                    throw new Error(error);
                }
            });

            // initialize jqxGrid
            $("#jqxgrid").jqxGrid({
                width: 550,
                source: dataadapter,
                filterable: true,
                sortable: true,
                autoheight: true,
                pageable: true,
                pagesize: 3,
                pagesizeoptions: ['3', '4', '5'],
                virtualmode: true,
                rendergridrows: function (obj) {
                    return obj.data;
                },
                columns: [{
                    text: 'First Name',
                    datafield: 'FirstName',
                    width: 100
                }, {
                    text: 'Last Name',
                    datafield: 'LastName',
                    width: 100
                }, {
                    text: 'Title',
                    datafield: 'Title',
                    width: 180
                }, {
                    text: 'Birth Date',
                    datafield: 'BirthDate',
                    cellsformat: 'yyyy-MM-dd',
                    align: 'right',
                    cellsalign: 'right'
                }]
            });
        });
    </script>
</head>
<body class='default'>
    <div id="jqxgrid">
    </div>
</body>
</html>